/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.ImplementInterface;

import DAL.DBManager;
import Server.DTO.Customer_DTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author 10520_000
 */
public class CustomerRepository {

    /**
     * Insert customer
     *
     * @param info
     * @return
     */
    public static int Insert(Customer_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String sql = "INSERT INTO Customer(CustomerID, FullName, IDNumber, Address, Email, Phone) "
                    + "Values('"
                    + info.getCustomerID() + "','"
                    + info.getFullName() + "','"
                    + info.getIDNumber() + "','"
                    + info.getHomeAddress() + "','"
                    + info.getEmail() + "','"
                    + info.getPhone() + "')";
            Statement statement = con.createStatement();
            statement.executeUpdate(sql);
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    /**
     * Update customer
     *
     * @param info
     * @return
     */
    public static int Update(Customer_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String sql = "UPDATE Customer "
                    + "SET FullName='" + info.getFullName() + "',"
                    + "IDNumber='" + info.getIDNumber() + "',"
                    + "Address='" + info.getHomeAddress() + "',"
                    + "Email='" + info.getEmail() + "',"
                    + "Phone='" + info.getPhone() + "' "
                    + "where CustomerID='" + info.getCustomerID() + "'";
            Statement statement = con.createStatement();
            statement.executeUpdate(sql);
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    /**
     * Delete customer
     *
     * @param info
     * @return
     */
    public static int Delete(Customer_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "DELETE FROM Customer WHERE CustomerID=?";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getCustomerID());

            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }

    public static void DeleteAll() {
        Connection con = DBManager.getInstance().getConnection();
        try {
            con.setAutoCommit(false);
            String SQL = "DELETE FROM Customer";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.executeUpdate();
            con.commit();
        } catch (SQLException se) {
            try {
                con.rollback();
            } catch (SQLException ise) {
            }
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException fse) {
            }
        }
    }

    public static ArrayList FindAll() {
        ArrayList arr = new ArrayList();

        try {
            String sql = "SELECT * FROM Customer ORDER BY CustomerID";
            Connection con = DBManager.getInstance().getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                Customer_DTO info = new Customer_DTO();
                info.setCustomerID(rs.getString("CustomerID"));
                info.setFullName(rs.getString("FullName"));
                info.setIDNumber(rs.getString("IDNumber"));
                info.setHomeAddress(rs.getString("Address"));
                info.setEmail(rs.getString("Email"));
                info.setPhone(rs.getString("Phone"));
                arr.add(info);
            }

            stmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }

    public static Customer_DTO FindByCustomerID(String str_CustomerID) {
        Customer_DTO info = new Customer_DTO();

        try {
            String sql = "SELECT * FROM Customer WHERE CustomerID = '" + str_CustomerID + "'";
            Connection con = DBManager.getInstance().getConnection();
            ResultSet rs = con.createStatement().executeQuery(sql);
            while (rs.next()) {
                info.setCustomerID(rs.getString("CustomerID"));
                info.setFullName(rs.getString("FullName"));
                info.setIDNumber(rs.getString("IDNumber"));
                info.setHomeAddress(rs.getString("Address"));
                info.setEmail(rs.getString("Email"));
                info.setPhone(rs.getString("Phone"));
            }
        } catch (SQLException se) {
            System.out.println(se);
        }
        return info;
    }

    public static String GetCustomerID(String str_fullname, String str_cmnd) throws SQLException {
        String result = "";
        String sql = "select CustomerID from Customer where FullName='" + str_fullname + "' and IDNumber='" + str_cmnd + "'";
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) {
            result = rs.getString("CustomerID");
        }
        return result;
    }

    public static ArrayList GetCustomers(Customer_DTO info) throws SQLException {
        ArrayList arr = new ArrayList();
        String sql = "select * from Customer where "
                + "FullName like '%" + info.getFullName() + "%' and "
                + "IDNumber like '%" + info.getIDNumber() + "%'";
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while(rs.next())
        {
            Customer_DTO result = new Customer_DTO();
            result.setCustomerID(rs.getString("CustomerID"));
            result.setFullName(rs.getString("FullName"));
            result.setIDNumber(rs.getString("IDNumber"));
            result.setHomeAddress(rs.getString("Address"));
            result.setEmail(rs.getString("Email"));
            result.setPhone(rs.getString("Phone"));
            arr.add(result);
        }
        return arr;
    }

    public static void main(String[] str) throws SQLException {
        System.out.println(GetCustomerID("DO NGOC SAM", "1"));
    }
}
